IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InjectArraMasterData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InjectArraMasterData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
CREATE Procedure [dbo].[InjectArraMasterData] 
/*
**	Injects all the Arra master table data from InfoEd.  This includes:
	ArraGovAgency
	ArraProgramSource
	ArraActivity
*/
As
Begin

Set nocount on

Begin Try 

Begin Transaction InjectArraMasterData

/*-----------------------------------------------------------------------------
	M A P  A R R A  M A S T E R  D A T A
-----------------------------------------------------------------------------*/
Update s set ArraGovAgencyID = aga.ID
from [dbo].[stg_ie_Arra_GovAgency] s
inner join [dbo].[ArraGovAgency] aga on aga.Code = s.Code

Update s set ArraProgramSourceID = aps.ID
from [dbo].[stg_ie_Arra_ProgramSource] s
inner join [dbo].[ArraProgramSource] aps on aps.Code = s.Code

Update s set ArraActivityCodeID = aac.ID
from [dbo].[stg_ie_Arra_ActivityCode] s
inner join [dbo].[ArraActivity] aac on aac.Code = s.Code


declare @MaxID int
/*-----------------------------------------------------------------------------------
Seed New Activity Codes in staging
-----------------------------------------------------------------------------------*/
if object_id('tempdb..#NewCodes') is not null drop table #NewCodes
Create table #NewCodes(ID int identity, code varchar(20))

INSERT INTO #NewCodes([Code])
Select distinct s.Code From [dbo].[Stg_IE_ARRA_ActivityCode] s
left outer join [dbo].[ArraActivity] a on a.code = s.Code
where a.code is null and IsNull(s.Code,'')<>''

set @MaxID = isnull((select max(ID) from [dbo].[ArraActivity]),0)
Update s Set NewArraActivityCodeID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_ActivityCode] s
inner join #NewCodes t on t.code = s.Code

/*-----------------------------------------------------------------------------------
Seed New Gov Agency Codes in staging
-----------------------------------------------------------------------------------*/
truncate table #NewCodes

INSERT INTO #NewCodes([Code])
Select distinct s.Code From [dbo].[Stg_IE_ARRA_GovAgency] s
left outer join [dbo].[ArraGovAgency] a on a.code = s.Code
where a.code is null and isnull(s.Code,'')<>''

set @MaxID = isnull((select max(ID) from [dbo].[ArraGovAgency]),0)
Update s Set NewArraGovAgencyID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_GovAgency] s
inner join #NewCodes t on t.code = s.Code

/*-----------------------------------------------------------------------------------
Seed New Program Source Codes in staging. 
-----------------------------------------------------------------------------------*/
truncate table #NewCodes

INSERT INTO #NewCodes([Code])
Select distinct s.Code From [dbo].[Stg_IE_ARRA_ProgramSource] s
left outer join [dbo].[ArraProgramSource] a on a.code = s.Code
where a.code is null and isnull(s.Code,'')<>''

Update s Set NewArraProgramSourceID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_ProgramSource] s
inner join #NewCodes t on t.code = s.Code

drop table #NewCodes

UPDATE g
   SET [Code] = s.code
      ,[Name] = s.[description]
From [dbo].[ArraGovAgency] g
inner join [dbo].[Stg_ie_Arra_GovAgency] s on s.ArraGovAgencyID = g.ID


INSERT INTO [dbo].[ArraGovAgency]
           ([Code]
           ,[Name])
Select s.code, s.[description] From [dbo].[Stg_ie_Arra_GovAgency] s
where coalesce(s.NewArraGovAgencyID,0) <> 0

UPDATE p
   SET [Code] = s.code
      ,[Description] = s.[description]
From [dbo].[ArraProgramSource] p
inner join [dbo].[Stg_ie_Arra_ProgramSource] s on s.ArraProgramSourceID = p.ID


INSERT INTO [dbo].[ArraProgramSource]
           ([Code]
           ,[Description])
Select s.code, s.[description] From [dbo].[Stg_ie_Arra_ProgramSource] s
where coalesce(s.NewArraProgramSourceID,0) <> 0

UPDATE p
   SET [Code] = s.code
      ,[Description] = s.[description]
From [dbo].[ArraActivity] p
inner join [dbo].[Stg_ie_Arra_ActivityCode] s on s.ArraActivityCodeID = p.ID


INSERT INTO [dbo].[ArraActivity]
           ([Code]
           ,[Description])
Select s.code, s.[description] From [dbo].[Stg_ie_Arra_ActivityCode] s
where coalesce(s.NewArraActivityCodeID,0) <> 0

Commit Transaction InjectArraMasterData

End Try

Begin Catch
Declare 
    @ErrorMessage    NVARCHAR(4000),
    @ErrorNumber     INT,
    @ErrorSeverity   INT,
    @ErrorState      INT,
    @ErrorLine       INT,
    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that 
-- capture information for RAISERROR.
Select 
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original
-- error information.
Select @ErrorMessage = 
    N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
        'Message: '+ ERROR_MESSAGE();

If XACT_STATE() <> 0 Rollback Transaction InjectArraMasterData

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
Raiserror
    (
    @ErrorMessage, 
    @ErrorSeverity, 
    1,               
    @ErrorNumber,    -- parameter: original error number.
    @ErrorSeverity,  -- parameter: original error severity.
    @ErrorState,     -- parameter: original error state.
    @ErrorProcedure, -- parameter: original error procedure name.
    @ErrorLine       -- parameter: original error line number.
    );
End Catch;


END 
GO 
